{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "6dd61309",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd,xlwings as xw"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "a7386502",
   "metadata": {},
   "outputs": [],
   "source": [
    "#需要用到的path的路径\n",
    "\n",
    "商城订单_p=r'K:\\BaiduSyncdisk\\王振洋资料\\1月商贸分公司资料\\5本月退货-跑男商城\\商城订单导出-退货-2.01(81-90、7#).xlsx'  #系统导出表格，字段一般不会变\n",
    "商品管理_p=r'商品管理1.25.xlsx'   #系统导出表格，字段一般不会变\n",
    "基础数据_p=r'K:\\BaiduSyncdisk\\王振洋资料\\1月商贸分公司资料\\t+基础数据金水分.xlsx'   #手工维护的表格，字段可能会变\n",
    "销售出库单_p=r'J:\\王振洋资料\\1月商贸分资料\\0.t+模版\\商贸分销售出库单-模版.xlsx'  #空白的销售出库单，有时候可以用到\n",
    "凭证导入导出_p=r'J:\\王振洋资料\\1月商贸分资料\\0.t+模版\\商贸分凭证导入导出-模版.xlsx' #空白的凭证导入导出模版，可能可以用到\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ea20dffa",
   "metadata": {},
   "source": [
    "# 商城订单导入与预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "35b5893a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开导出的<商城订单>.xlsx\n",
    "wb_商城订单= xw.Book(商城订单_p)     #打开商城订单表\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "4d2c304f",
   "metadata": {},
   "outputs": [],
   "source": [
    "'''这些都是系统导出的表格的Sheet名字和字段，一般不会改变'''\n",
    "# 读取跑男商城订单sheet数据为df\n",
    "sheet_name='跑男商城订单'\n",
    "df1=wb_商城订单.sheets(sheet_name).range(\"a1\").expand('table').options(pd.DataFrame).value\n",
    "df2=(\n",
    "# 对订单属于按城市名称、商品编号、商品名称进行分组操作\n",
    "    df1.groupby(by=[\"城市名称\",\"商品编号\",\"商品名称\"])[[\"数量\",\"在线支付时价格\",\"混合支付时的在线支付金额\"]].sum()\n",
    "    .assign(在线支付金额合计=lambda x:x['在线支付时价格'] + x['混合支付时的在线支付金额'])\n",
    "    .reset_index()\n",
    "    )\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ecae4bb3",
   "metadata": {},
   "source": [
    "# 套装拆件\n",
    "导出商品管理中的套装sheet,匹配出其中在订单中有使用过的套装，计算其中单品金额占比。"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 取出有使用的套装的编号并去重"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "b454dc91",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 取出商品编号列，并去重，得到所有使用过的商品编号构成的非重复数组\n",
    "\n",
    "lista=df2['商品编号'].drop_duplicates().tolist()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 商品管理表导入与预处理"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "f01b34b7",
   "metadata": {},
   "outputs": [],
   "source": [
    "''''''\n",
    "\n",
    "wb_商品管理=xw.Book(r'商品管理1.25.xlsx')  #打开商品管理表 \n",
    "\n",
    "# 从<商品管理>.xlsx 读取商城系统导出的套装的数据\n",
    "单品=wb_商品管理.sheets('单品').range('a1').expand().options(pd.DataFrame).value\n",
    "套装=wb_商品管理.sheets('套装').range('a1').expand().options(pd.DataFrame).value\n",
    "# 合并单品与套装并筛选有使用过的套装\n",
    "concat_df=pd.concat([单品,套装]).reset_index().query('商品编号 in @lista')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 在跑男商城订单中写入 套装单品 一列"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 这个就是简单的加一个 列在<跑男商城订单> 这一sheet里，没啥影响\n",
    "merge_df=pd.merge(df1,concat_df.drop_duplicates(subset='商品编号'),on='商品编号',how='left')\n",
    "wb_商城订单.sheets(sheet_name).range('w1').value=merge_df['单品套装']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 计算套装中的 '单品金额占比'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "dd2d1948",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "'如果是单品，那么套装中单品数量=1，套装中单品金额占比=1'"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# 对套装df进行groupby 操作，并循环遍历，按每一套装，计算其中单品的价格占比\n",
    "result=pd.DataFrame()\n",
    "for i,j in concat_df.groupby(by=['商品编号','商品名称']):\n",
    "    j.reset_index(drop=True,inplace=True)\n",
    "    j['单品价格（数量×单价）']=j['套装中单品数量']*j['套装中单品原价(元)']\n",
    "    j['套装价（单品价格和）']=j['单品价格（数量×单价）'].sum()\n",
    "    j['单品金额占比']=round(j['单品价格（数量×单价）'] / j['套装价（单品价格和）'],2)\n",
    "    row=len(j.index)-1  # 最后一行的Index索引\n",
    "    j.loc[row,'单品金额占比']=round(1-(j['单品金额占比'].sum()-j.loc[row,'单品金额占比']),4)\n",
    "    j['验证金额占比']=j['单品金额占比'].sum()\n",
    "    result=pd.concat([result,j])\n",
    "\n",
    "\n",
    "\n",
    "# 为单品添加 套装中单品编号、套装中单品名称、套装中单品数量、套装中单品原价、套装中单品\n",
    "result['套装中单品编号'] = result.apply(lambda x:x['商品编号'] if x['单品套装']=='单品' else x['套装中单品编号'],axis=1)\n",
    "result['套装中单品名称'] = result.apply(lambda x:x['商品名称'] if x['单品套装']=='单品' else x['套装中单品名称'],axis=1)\n",
    "result['套装中单品数量'] = result.apply(lambda x:1 if x['单品套装']=='单品' else x['套装中单品数量'],axis=1)\n",
    "result['单品金额占比']= result.apply(lambda x:1 if x['单品套装']=='单品' else x['单品金额占比'],axis=1)\n",
    "'''如果是单品，那么套装中单品数量=1，套装中单品金额占比=1'''\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1e9402c0",
   "metadata": {},
   "source": [
    "## 匹配 商城订单 与 商品管理 ，为套装拆价"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "c0914034",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 合并 商城订单 与 商品管理\n",
    "df3 = pd.merge(df2.reset_index(),result,on=[\"商品编号\"],how='left')  #已完成了套装拆件"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 套装拆成单品后，重新计算 单品的销售数量 和 销售金额"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "7727b33a",
   "metadata": {},
   "outputs": [],
   "source": [
    "# 套装拆成件后，更新单品的数量和 单品的金额\n",
    "\n",
    "df3['new_数量']=df3['数量']*df3['套装中单品数量']  # 套装拆件后单品销售数量=套装销售数量×套装中单品数量\n",
    "df3['new_合计金额']=df3['在线支付金额合计'] *df3['单品金额占比']  # 套装拆件后单品销售金额 =套装售价 × 套装价格中单品金额占比\n",
    " \n",
    "\n",
    "\n",
    "# 将拆件后的数据写入“套装拆件中间表”，用于数据检查或补充\n",
    "'''套装拆件中间表中的 new_合计金额 应该可以与 跑男商城订单中的金额验证''' \n",
    "wb_商城订单.sheets('套装拆件中间表').cells.clear()\n",
    "wb_商城订单.sheets('套装拆件中间表').cells.number_format = '@'\n",
    "wb_商城订单.sheets('套装拆件中间表').range('a1').value=df3\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 编码匹配中间表"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 匹配仓库、部门、客户、存货编码、科目 等 各项内容\n",
    "\n",
    "\n",
    "\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从套装拆件中间表读取数据\n",
    "df3=wb_商城订单.sheets('套装拆件中间表').range('a1').expand().options(pd.DataFrame).value"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "\n",
    "# 取出匹配编码等需要使用的数据\n",
    "df3=df3.loc[:,['单品套装','城市名称','套装中单品名称','new_数量','new_合计金额']]\n",
    "df3=df3.rename(columns={'套装中单品名称':'商品名称','new_数量':'数量','new_合计金额':'合计金额'})\n",
    "# 重新按城市和 商品名称分组压缩一下\n",
    "df3=df3.groupby(by=['城市名称','商品名称'])[['数量','合计金额']].sum().reset_index()\n",
    "\n",
    "# 添加一个城市2列\n",
    "df3['城市2']=df3['城市名称'].str.replace(\"市\",\"\") #去掉城市名称中的“市”字，用于部门和仓库匹配\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 打开t+基础数据表并写入相关数据，为匹配做准备\n",
    "wb基础数据=xw.Book(基础数据_p)\n",
    "仓库档案=wb基础数据.sheets('仓库档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "部门档案=wb基础数据.sheets('部门档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=wb基础数据.sheets('存货档案').range('a1').expand().options(pd.DataFrame,index=False).value\n",
    "存货档案=存货档案.loc[:,['存货编码','存货名称','计量单位','收入科目编码','收入科目名称']]\n",
    "往来单位档案=wb基础数据.sheets(\"往来单位档案\").range('a1').expand().options(pd.DataFrame,index=False).value\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 匹配 存货编码、仓库编码、部门编码等\n",
    "合并=pd.merge(df3,仓库档案,left_on='城市2',right_on='仓库名称',how='left')\n",
    "合并=pd.merge(合并,部门档案,left_on='城市2',right_on='部门',how='left')\n",
    "合并=pd.merge(合并,存货档案,left_on='商品名称',right_on='存货名称',how='left')\n",
    "合并['备注']=input('请输入备注前缀') + \"-\" + 合并['城市2']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 将数据写入到编码匹配中间表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 将合并写入<编码匹配中间表>\n",
    "\n",
    "# 创建一个sheet名字构建的列表，用于后续判断\n",
    "sheet_name=[wb_商城订单.sheets[i].name for i in range(wb_商城订单.sheets.count)]\n",
    "# 将数据写入到编码匹配中间表，对匹配失败的数据手动补充\n",
    "if '编码匹配中间表' in sheet_name:\n",
    "    ws=wb_商城订单.sheets('编码匹配中间表')\n",
    "    ws.cells.clear()\n",
    "    ws.cells.number_format = '@'\n",
    "    ws.range('a1').value=合并\n",
    "else:\n",
    "    new_sheet=wb_商城订单.sheets.add('编码匹配中间表')\n",
    "    new_sheet.cells.number_format = '@'\n",
    "    new_sheet.range('a1').value=合并\n",
    "    "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 模版数据源表\n",
    "在编码匹配中间表补充好数据的编码、科目等数据信息等后，就可以将数据粘贴到模版数据源表，并进一步补充“不含税金额”，“税额”，“往来单位等”。补充完成后，以模版数据源为基础，生成销售出库单和凭证导入导出表中的数据\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据写入到销售出库单"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "数据源=wb_商城订单.sheets('模版数据源').range('a1').expand('table').options(pd.DataFrame).value\n",
    "凭证模版=wb_商城订单.sheets('凭证导入导出')\n",
    "出库单模版=wb_商城订单.sheets(\"销售出库单\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "出库单模版.range('a5:af10000').clear_contents()\n",
    "出库单模版.range(\"g3:j3\").value=数据源.loc[:,['往来单位编码','往来单位','部门编码','部门']].values\n",
    "出库单模版.range('s3:t3').value=数据源.loc[:,['仓库编码','仓库名称']].values\n",
    "出库单模版.range('w3:x3').value=数据源.loc[:,['存货编码','存货名称']].values\n",
    "出库单模版.range('z3:aa3').value=数据源.loc[:,['计量单位','数量']].values\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 数据写入到凭证导入导出"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 读取df\n",
    "\n",
    "数据源=wb_商城订单.sheets('模版数据源').range('a1').expand().options(pd.DataFrame).value\n",
    "凭证模版=wb_商城订单.sheets('凭证导入导出')\n",
    "出库单模版=wb_商城订单.sheets(\"销售出库单\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {},
   "outputs": [],
   "source": [
    "def 按组添加往来科目和销项税科目(df):\n",
    "    # 应收账款的数据\n",
    "    应收金额=df['合计金额'].sum()\n",
    "    往来单位编码=df.loc[1,'往来单位编码'] #读取往来单位编码数据\n",
    "    往来单位名称=df.loc[1,'往来单位'] #读取往来单位的名称\n",
    "    data={'科目编码':'1122','科目':'','币种':'人民币','借贷方向':'借方','本币':应收金额,'往来单位编码':往来单位编码,'往来单位':往来单位名称,'部门编码':'','部门':''}\n",
    "    应收数据=pd.DataFrame(data,index=[0])\n",
    "    \n",
    "    # 收入的数据\n",
    "    收入数据=df.loc[:,['科目编码','科目','不含税金额','往来单位编码','往来单位','部门编码','部门',]]\n",
    "    收入数据['币种']='人民币'\n",
    "    收入数据['借贷方向']='贷方'\n",
    "    收入数据['往来单位编码']=''\n",
    "    收入数据['往来单位']=''\n",
    "    收入数据['本币']=收入数据['不含税金额']\n",
    "    收入数据=收入数据.loc[:,['科目编码','科目','币种','借贷方向','本币','往来单位编码','往来单位','部门编码','部门']]\n",
    "\n",
    "    # 销项税的数据\n",
    "    销项金额=df['税额'].sum()\n",
    "    data={'科目编码':'22210106','科目':'','币种':'人民币','借贷方向':'贷方','本币':销项金额,'往来单位编码':'','往来单位':'','部门编码':'','部门':''}\n",
    "    销项数据=pd.DataFrame(data,index=[1])\n",
    "\n",
    "    合并=pd.concat([应收数据,收入数据,销项数据])\n",
    "    return 合并\n",
    "\n",
    "    "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [],
   "source": [
    "凭证导入导出=数据源.groupby(by=['往来单位编码']).apply(按组添加往来科目和销项税科目)  \n",
    "凭证导入导出['原币']=凭证导入导出['本币']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [],
   "source": [
    "# 从凭证导入导出将数据写入到《凭证导入导出》\n",
    "凭证模版.range('a4:ab10000').clear_contents()\n",
    "凭证模版.range('h2:j2').value=凭证导入导出.loc[:,['科目编码','科目','币种']].values  \n",
    "凭证模版.range('m2:o2').value=凭证导入导出.loc[:,['借贷方向','原币','本币']].values\n",
    "凭证模版.range('y2:ab2').value=凭证导入导出.loc[:,['往来单位编码','往来单位','部门编码','部门']].values\n",
    "\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.11.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
